{
 "cells": [
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Panel\n",
    "pandas的panel是由items, major_xs, minor_xs构成的三维数据，方便管理三维数据。\n",
    "multiIndex:一种多索引的数据格式，可将三维数据直观展示出来"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 1,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "import warnings\n",
    "warnings.filterwarnings('ignore')"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### 1、 字典转Panel"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "metadata": {
    "scrolled": true
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "<class 'pandas.core.panel.Panel'>\n",
      "Dimensions: 3 (items) x 34 (major_axis) x 5 (minor_axis)\n",
      "Items axis: 600036.XSHG to 601318.XSHG\n",
      "Major_axis axis: 2017-03-21 15:00:00 to 2017-05-10 15:00:00\n",
      "Minor_axis axis: close to volume\n"
     ]
    }
   ],
   "source": [
    "from datetime import datetime\n",
    "import pandas as pd\n",
    "symbol=['600036.XSHG','600050.XSHG','601318.XSHG']\n",
    "data_dict = {}\n",
    "for s in symbol:\n",
    "    data =  pd.read_excel('sz50.xlsx',sheetname=s, index_col='datetime')\n",
    "    data_dict[s] = data.loc['2017-03-21':'2017-05-10']\n",
    "PN = pd.Panel(data_dict)\n",
    "print(PN)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### 2、 显示数据 to_frame()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "metadata": {
    "scrolled": true
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "                            600036.XSHG   600050.XSHG  601318.XSHG\n",
      "datetime            minor                                         \n",
      "2017-03-21 15:00:00 close         72.36  9.270000e+00        81.28\n",
      "                    high          72.67  9.360000e+00        81.28\n",
      "                    low           71.97  9.210000e+00        80.34\n",
      "                    open          72.36  9.320000e+00        80.70\n",
      "                    volume  40184305.00  1.473474e+08  96990107.00\n",
      "                            600036.XSHG  600050.XSHG  601318.XSHG\n",
      "datetime            minor                                        \n",
      "2017-03-21 15:00:00 close          72.4          9.3         81.3\n",
      "                    high           72.7          9.4         81.3\n",
      "                    low            72.0          9.2         80.3\n",
      "                    open           72.4          9.3         80.7\n",
      "                    volume   40184305.0  147347370.0   96990107.0\n"
     ]
    }
   ],
   "source": [
    "MI = PN.to_frame()\n",
    "print(MI.head())\n",
    "print(round(MI.head(),1))"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### 3、 修改顺序 transpose()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "metadata": {
    "scrolled": true
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "<class 'pandas.core.panel.Panel'>\n",
      "Dimensions: 5 (items) x 34 (major_axis) x 3 (minor_axis)\n",
      "Items axis: close to volume\n",
      "Major_axis axis: 2017-03-21 15:00:00 to 2017-05-10 15:00:00\n",
      "Minor_axis axis: 600036.XSHG to 601318.XSHG\n",
      "                                 close   high    low   open       volume\n",
      "datetime            minor                                               \n",
      "2017-03-21 15:00:00 600036.XSHG  72.36  72.67  71.97  72.36   40184305.0\n",
      "                    600050.XSHG   9.27   9.36   9.21   9.32  147347370.0\n",
      "                    601318.XSHG  81.28  81.28  80.34  80.70   96990107.0\n",
      "2017-03-22 15:00:00 600036.XSHG  71.51  72.36  71.31  72.32   46855076.0\n",
      "                    600050.XSHG   9.26   9.36   8.97   9.21  231158696.0\n"
     ]
    }
   ],
   "source": [
    "MI_tp = PN.transpose(2,1,0)\n",
    "print(MI_tp)\n",
    "MI_tp = PN.transpose(2,1,0).to_frame()\n",
    "print(MI_tp.head())"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### 修改名称"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "PN_rename = PN.rename(items={'600036.XSHG':'ZSYH','600050.XSHG':'ZGLT','601318.XSHG':'ZGPA'})"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 6,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "<class 'pandas.core.panel.Panel'>\n",
      "Dimensions: 3 (items) x 34 (major_axis) x 5 (minor_axis)\n",
      "Items axis: ZSYH to ZGPA\n",
      "Major_axis axis: 2017-03-21 15:00:00 to 2017-05-10 15:00:00\n",
      "Minor_axis axis: close to volume\n"
     ]
    }
   ],
   "source": [
    "print(PN_rename)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## resample抽样取周线"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 7,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "<class 'pandas.core.panel.Panel'>\n",
      "Dimensions: 5 (items) x 8 (major_axis) x 3 (minor_axis)\n",
      "Items axis: close to volume\n",
      "Major_axis axis: 2017-03-27 00:00:00 to 2017-05-15 00:00:00\n",
      "Minor_axis axis: 600036.XSHG to 601318.XSHG\n"
     ]
    }
   ],
   "source": [
    "PN_RE = PN.transpose(2,1,0).resample('W-MON',axis=1).last()\n",
    "print(PN_RE)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 8,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "                        close   high    low   open       volume\n",
      "datetime   minor                                               \n",
      "2017-03-27 600036.XSHG  73.67  74.56  72.51  72.51   80019192.0\n",
      "           600050.XSHG   9.11   9.32   9.08   9.25  175576529.0\n",
      "           601318.XSHG  82.31  83.50  81.75  81.77  122855895.0\n",
      "2017-04-03 600036.XSHG  74.09  74.29  73.40  73.40   33962146.0\n",
      "           600050.XSHG   9.03   9.10   8.85   8.85  125608109.0\n"
     ]
    }
   ],
   "source": [
    "print(PN_RE.to_frame().head())"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### 4、 访问数据"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### 访问items的数据"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 9,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "            600036.XSHG  600050.XSHG  601318.XSHG\n",
      "datetime                                         \n",
      "2017-03-27        73.67         9.11        82.31\n",
      "2017-04-03        74.09         9.03        82.85\n",
      "2017-04-10        73.01          NaN        81.61\n",
      "2017-04-17        72.90          NaN        80.67\n",
      "2017-04-24        73.05          NaN        81.01\n"
     ]
    }
   ],
   "source": [
    "print(PN_RE['close'].head())"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### 访问major的数据"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 10,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "             close   high    low   open      volume\n",
      "600036.XSHG  73.01  73.21  72.16  73.05  32313532.0\n",
      "600050.XSHG    NaN    NaN    NaN    NaN         NaN\n",
      "601318.XSHG  81.61  82.22  81.48  81.88  48440496.0\n"
     ]
    }
   ],
   "source": [
    "print(PN_RE.major_xs('2017-04-10'))"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### 访问minor的数据"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 11,
   "metadata": {
    "scrolled": true
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "            close   high    low   open      volume\n",
      "datetime                                          \n",
      "2017-03-27  73.67  74.56  72.51  72.51  80019192.0\n",
      "2017-04-03  74.09  74.29  73.40  73.40  33962146.0\n",
      "2017-04-10  73.01  73.21  72.16  73.05  32313532.0\n",
      "2017-04-17  72.90  72.94  71.78  72.43  40747304.0\n",
      "2017-04-24  73.05  73.36  72.55  72.90  37143301.0\n"
     ]
    }
   ],
   "source": [
    "print(PN_RE.minor_xs('600036.XSHG').head())"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### 访问loc使用名称索引"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 12,
   "metadata": {
    "scrolled": true
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "                        close   high    low   open       volume\n",
      "datetime   minor                                               \n",
      "2017-03-27 600036.XSHG  73.67  74.56  72.51  72.51   80019192.0\n",
      "           600050.XSHG   9.11   9.32   9.08   9.25  175576529.0\n",
      "           601318.XSHG  82.31  83.50  81.75  81.77  122855895.0\n",
      "2017-04-03 600036.XSHG  74.09  74.29  73.40  73.40   33962146.0\n",
      "           600050.XSHG   9.03   9.10   8.85   8.85  125608109.0\n",
      "           601318.XSHG  82.85  83.18  81.93  82.15   63797819.0\n",
      "2017-04-10 600036.XSHG  73.01  73.21  72.16  73.05   32313532.0\n",
      "           601318.XSHG  81.61  82.22  81.48  81.88   48440496.0\n"
     ]
    }
   ],
   "source": [
    "print(PN_RE.loc[:,'2017-03-21':'2017-04-10',:].to_frame())"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### 访问iloc使用位置索引"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 13,
   "metadata": {
    "scrolled": true
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "<class 'pandas.core.panel.Panel'>\n",
      "Dimensions: 3 (items) x 6 (major_axis) x 1 (minor_axis)\n",
      "Items axis: low to volume\n",
      "Major_axis axis: 2017-04-10 00:00:00 to 2017-05-15 00:00:00\n",
      "Minor_axis axis: 601318.XSHG to 601318.XSHG\n"
     ]
    }
   ],
   "source": [
    "print(PN_RE.iloc[2:,2:,2:])"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 14,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "close    89.36\n",
      "high     89.63\n",
      "low      85.18\n",
      "Name: 601318.XSHG, dtype: float64\n"
     ]
    }
   ],
   "source": [
    "print(PN_RE.ix[0:3,-1,'601318.XSHG'])"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### 5、 处理缺失值"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 15,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "True\n"
     ]
    }
   ],
   "source": [
    "print(PN.isnull().values.any())"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 16,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "if PN.isnull().values.any():\n",
    "    PN.fillna(method='ffill',inplace=True)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 17,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "False\n"
     ]
    }
   ],
   "source": [
    "print(PN.isnull().values.any())"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "collapsed": true
   },
   "source": [
    "### 6、 多维数据计算与合并"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### 计算每只股票的5日均线，并且合并成DataFrame"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 18,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "                     600036.XSHG  600050.XSHG  601318.XSHG\n",
      "datetime                                                  \n",
      "2017-05-04 15:00:00       73.158         9.03       84.886\n",
      "2017-05-05 15:00:00       73.012         9.03       84.626\n",
      "2017-05-08 15:00:00       72.758         9.03       84.544\n",
      "2017-05-09 15:00:00       72.580         9.03       84.580\n",
      "2017-05-10 15:00:00       72.650         9.03       85.430\n"
     ]
    }
   ],
   "source": [
    "import talib.abstract as ta\n",
    "\n",
    "df_ma = pd.DataFrame({name: ta.MA(value, 5) for name, value in PN.iteritems()})\n",
    "print(df_ma.tail())"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### 计算每只股票的macd, 然后合并成MultiIndex"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 19,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "                                     macd  macdsignal  macdhist\n",
      "datetime            minor                                      \n",
      "2017-05-10 15:00:00 600036.XSHG -0.067689   -0.105095  0.037406\n",
      "                    600050.XSHG -0.019533   -0.027102  0.007569\n",
      "                    601318.XSHG  1.090898    0.361969  0.728928\n"
     ]
    }
   ],
   "source": [
    "pn_macd = pd.Panel({name: ta.MACD(value) for name, value in PN.iteritems()})\n",
    "df_macd = pn_macd.transpose(2,1,0).to_frame().head()\n",
    "print(df_macd)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### 用stack()将DataFrame转换multiIndex，再将两个multiIndex合并"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 20,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "                                     macd  macdsignal  macdhist     ma\n",
      "datetime            minor                                             \n",
      "2017-05-10 15:00:00 600036.XSHG -0.067689   -0.105095  0.037406  72.65\n",
      "                    600050.XSHG -0.019533   -0.027102  0.007569   9.03\n",
      "                    601318.XSHG  1.090898    0.361969  0.728928  85.43\n"
     ]
    }
   ],
   "source": [
    "df_macd['ma'] = df_ma.stack()\n",
    "print(df_macd)"
   ]
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "Python 3",
   "language": "python",
   "name": "python3"
  },
  "language_info": {
   "codemirror_mode": {
    "name": "ipython",
    "version": 3
   },
   "file_extension": ".py",
   "mimetype": "text/x-python",
   "name": "python",
   "nbconvert_exporter": "python",
   "pygments_lexer": "ipython3",
   "version": "3.6.1"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 2
}
